package com.twang.stock.service;

import com.twang.stock.dto.ZuoYeRankDTO;
import com.twang.stock.entity.ZuoYeRank;
import com.twang.stock.entity.ZuoYeSumbit;
import com.twang.stock.mapper.ZuoYeRankMapper;
import com.twang.stock.mapper.ZuoYeSumbitMapper;
import com.twang.stock.redis.RedisUtil;
import com.twang.stock.utils.date.HolidayCopy;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 排名
 */
@Slf4j
@Service
public class ZuoYeRankService {
    @Resource
    private ZuoYeRankMapper zuoYeRankMapper;

    @Resource
    private ZuoYeSumbitMapper zuoYeSumbitMapper;


    @Autowired
    private RedisUtil redisUtil;

    public static String ZUO_YE_RANK_LIST="ZUO_YE_RANK_LIST_";

    /**
     * 查看
     * @return
     */
    public List<ZuoYeRank> getRankList() {
        List<String> dates=zuoYeRankMapper.queryLastDate();
        List<ZuoYeRank> rankList = zuoYeRankMapper.querylist(dates.get(dates.size()-1));
        return rankList;
    }

    /**
     * 查看-id
     * @return
     */
    public ZuoYeRank queryById(Integer id) {
        ZuoYeRank zuoYeRank = zuoYeRankMapper.selectByPrimaryKey(id);
        return zuoYeRank;
    }

    /**
     * 查看-id
     * @return
     */
    public ZuoYeRank addUser(ZuoYeRank zuoYeRank) {
        zuoYeRank.setUserName(zuoYeRank.getUserName());
        zuoYeRank.setDate(HolidayCopy.getRealToday());
        zuoYeRank.setInitialAsset(zuoYeRank.getTodayAsset());
        zuoYeRank.setOrgAsset(zuoYeRank.getTodayAsset());
        zuoYeRank.setTotalYield(BigDecimal.ZERO);
        zuoYeRank.setTodayAsset(zuoYeRank.getTodayAsset());
        zuoYeRank.setTodayYield(BigDecimal.ZERO);
        zuoYeRank.setTodayHold(zuoYeRank.getTodayHold());
        zuoYeRank.setYesterdayYield(BigDecimal.ZERO);
        zuoYeRank.setYesterdayHold("新报单");
        zuoYeRank.setYesterdayAsset(zuoYeRank.getTodayAsset());
        zuoYeRank.setIntegral(0);
        zuoYeRank.setOutInMoney(BigDecimal.ZERO);
        zuoYeRank.setStatus("0");
        zuoYeRank.setCreateTime(new Date());
        zuoYeRank.setUpdateTime(new Date());
        zuoYeRankMapper.insert(zuoYeRank);
        return zuoYeRank;
    }

    /**
     * 查看-id
     * @return
     */
    public ZuoYeRank updateRankData(ZuoYeRank request ) {
        ZuoYeRank zuoYeRank = zuoYeRankMapper.selectByPrimaryKey(request.getId());
        if (null != zuoYeRank) {
            zuoYeRank.setUserName(request.getUserName());
            zuoYeRank.setDate(request.getDate());
            zuoYeRank.setInitialAsset(request.getInitialAsset());
            zuoYeRank.setOrgAsset(request.getOrgAsset());
            zuoYeRank.setTotalYield(request.getTotalYield());
            zuoYeRank.setTodayAsset(request.getTodayAsset());
            zuoYeRank.setTodayYield(request.getTodayYield());
            zuoYeRank.setTodayHold(request.getTodayHold());
            zuoYeRank.setYesterdayYield(request.getYesterdayYield());
            zuoYeRank.setYesterdayHold(request.getYesterdayHold());
            zuoYeRank.setYesterdayAsset(request.getYesterdayAsset());
            zuoYeRank.setIntegral(request.getIntegral());
            zuoYeRank.setOutInMoney(request.getOutInMoney());
            zuoYeRank.setUpdateTime(new Date());
            zuoYeRankMapper.updateByPrimaryKey(zuoYeRank);
        }
        return zuoYeRank;
    }

    @Transactional(rollbackFor = Exception.class)
    public void excelFile(MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Boolean indexTitle=Boolean.TRUE;//
        // 迭代行和列来读取数据
        for (Row row : sheet) {
            if (indexTitle) {
                indexTitle = Boolean.FALSE;
                continue;
            }
            ZuoYeSumbit zuoYeSumbit = new ZuoYeSumbit();
            try{
                String name = row.getCell(0).getStringCellValue();
                String money = row.getCell(1).getStringCellValue();
                String stockDetail = row.getCell(2).getStringCellValue();
                String ccmx =extractHyperlink(row.getCell(3));
                String outMoney = row.getCell(4)==null?"":row.getCell(4).getStringCellValue();

                zuoYeSumbit.setName(name);
                zuoYeSumbit.setDate(HolidayCopy.getRealToday());
                zuoYeSumbit.setMoney(new BigDecimal(money));
                zuoYeSumbit.setStockDetail(StringUtils.isEmpty(stockDetail) ? "空" : stockDetail);
                zuoYeSumbit.setOutMoney(StringUtils.isEmpty(outMoney) ? BigDecimal.ZERO : new BigDecimal(outMoney));
                zuoYeSumbit.setCcmx(ccmx);
                zuoYeSumbit.setType("SG");
                zuoYeSumbit.setCreateTime(new Date());
                zuoYeSumbit.setUpdateTime(new Date());
            }catch (Exception e){
                log.error("Exception e",e);
                throw e;
            }
            zuoYeSumbitMapper.insert(zuoYeSumbit);
        }

    }
    public String extractHyperlink(Cell cell) {
        // 检查单元格是否为公式类型
            // 尝试获取单元格的公式
            FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
            CellValue cellValue = evaluator.evaluate(cell);
            // 如果公式是超链接
            if (cell.getCellFormula().startsWith("HYPERLINK")) {
                // 获取公式字符串
                String formula = cell.getCellFormula();
                // 正则表达式用于提取HYPERLINK函数的第一个参数
                Pattern pattern = Pattern.compile("HYPERLINK\\(\"(.*?)\"");
                Matcher matcher = pattern.matcher(formula);
                if (matcher.find()) {
                    // 返回提取的URL
                    return matcher.group(1);
                }
            }
        return null;
    }


    /**
     * 查看持仓图片
     * @param userName
     * @param date
     * @param type : C-持仓，Z-转账
     * @return
     */
    public String getBase64Image(String userName,String date,String type) {
        StringBuffer image = new StringBuffer("");
        ZuoYeSumbit result = zuoYeSumbitMapper.queryLastRecord(userName, date);
        if (null != result) {
            if ("C".equals(type)) {
                image.append(result.getCcmx());
            } else if ("Z".equals(type)) {
                image.append(result.getZzjl());
            } else {
                image.append("未查找到记录！");
            }
        }
        return image.toString();
    }

    /**
     * 查看
     * @return
     */
    public Boolean checkUserName(String userName){
        List<String> dates=zuoYeRankMapper.queryLastDate();
        userName = userName.trim().replace(" ", "").replace(" ", "").toUpperCase();
        ZuoYeRank zuoYeRankResult = zuoYeRankMapper.queryuser( userName, dates.get(dates.size()-1));
        if(null!=zuoYeRankResult){
            return Boolean.TRUE;
        }else{
            return Boolean.FALSE;
        }
    }


    /**
     * 查看
     * @return
     */
    public List<ZuoYeRank> queryByName(String userName) {
        userName=userName.substring(0,4);
        userName = userName.trim().replace(" ", "").replace(" ", "").toUpperCase();
        List<ZuoYeRank> zuoYeRankResult = zuoYeRankMapper.queryByUserName(userName);
        return zuoYeRankResult;
    }


    /**
     * 删除报单
     * @return
     */
    public Boolean  deletedById(int userId,String dateTime){
        ZuoYeRank zuoYeRank=new ZuoYeRank();
        zuoYeRank.setId(userId);
        zuoYeRank.setDate(dateTime);
       List<ZuoYeRank> zuoYeRanks= zuoYeRankMapper.select(zuoYeRank);
       if(CollectionUtils.isEmpty(zuoYeRanks)){
           return Boolean.FALSE;
       }
        ZuoYeRank zuoYeRank1=zuoYeRanks.get(zuoYeRanks.size()-1);
        zuoYeRankMapper.deleteByPrimaryKey(userId);
        return Boolean.TRUE;
    }


    /**
     * 重新排名 根据日期
     * @return
     */
    public List<ZuoYeRank> doRank(){
        List<String> dates=zuoYeRankMapper.queryLastDate();
       String date= dates.get(dates.size()-1);
        List<ZuoYeRank> rankList=zuoYeRankMapper.querylistByYeild(date);
        for (int rank=0;rank<rankList.size();rank++) {
            ZuoYeRank zuoYeRank = rankList.get(rank);
            zuoYeRank.setRanks(rank + 1);
            zuoYeRankMapper.updateByPrimaryKey(zuoYeRank);
        }
//        //今日收益排第一的积分+1
//        List<ZuoYeRank> zuoYeRanks= zuoYeRankMapper.querylistOrderByTodayYeild(date);
//        if (CollectionUtils.isNotEmpty(zuoYeRanks)) {
//            ZuoYeRank zuoYeRank=zuoYeRanks.get(0);
//            zuoYeRank.setIntegral(zuoYeRank.getIntegral() + 1);
//            zuoYeRank.setUpdateTime(new Date());
//            zuoYeRankMapper.updateByPrimaryKey(zuoYeRank);
//        }
        return rankList;
    }


    /**
     * 转换
     * @param dataList
     * @return
     */
    public static Object[][] convertListToObjectArray(List<ZuoYeRankDTO> dataList) {
        Object[][] data = new Object[dataList.size()][];

        for (int i = 0; i < dataList.size(); i++) {
            ZuoYeRankDTO record = dataList.get(i);
            data[i] = new Object[]{
                    record.getRanks(),
                    record.getUserName()+"",
                    record.getOrgAsset(),
                    record.getInitialAsset(),
                    record.getTotalYield().multiply(new BigDecimal(100)).setScale(2, 2)+"%",
                    record.getTodayYield().multiply(new BigDecimal(100)).setScale(2, 2)+"%",
                    record.getTodayAsset(),
                    record.getTodayHold()+"",
                    record.getIntegral(),
                    record.getOutInMoney(),
                    record.getDate()
            };
        }

        return data;
    }

    /**
     * 下载
     * @return
     * @throws Exception
     */
    public Workbook downloadExcel() throws Exception {
        try {
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Data");
            // 创建加粗字体
            Font boldFont = workbook.createFont();
            boldFont.setBold(true);
            boldFont.setFontHeightInPoints((short) 30); // 设置字体大小为12点

            // 创建一个应用了自定义字体的单元格样式
            CellStyle customStyle = workbook.createCellStyle();
            customStyle.setFont(boldFont);

            // 创建标题行
            Row headerRow = sheet.createRow(0);
            headerRow.setHeightInPoints(20); // 设置行高为20点
            String[] columns = {"排名","选手", "原始资产","动态资产", "总收益率", "今日收益率","今日资产",  "今日持仓","积分","总出入金","日期"};

            // Creating header
            for (int i = 0; i < columns.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(columns[i]);
            }

            List<String> dates=zuoYeRankMapper.queryLastDate();
            List<ZuoYeRankDTO> rankList = zuoYeRankMapper.queryDownLoadlist(dates.get(dates.size()-1));
          //  List<ZuoYeRank> list = getRankList();

            // Sample data - replace with your data source
            Object[][] sampleData = convertListToObjectArray(rankList);

            int rowNum = 1;
            for (Object[] rowData : sampleData) {
                Row row = sheet.createRow(rowNum++);

                int colNum = 0;
                for (Object field : rowData) {
                    Cell cell = row.createCell(colNum++);
                    if (field instanceof String) {
                        cell.setCellValue((String) field);
                    } else if (field instanceof Integer) {
                        cell.setCellValue((Integer) field);
                    } else if (field instanceof BigDecimal) {
                        cell.setCellValue(((BigDecimal) field).doubleValue());
                    } else if (field instanceof Date) {
                        cell.setCellValue((Date) field);
                    }
                }
            }

            // Resize all columns to fit the content size
            for (int i = 0; i < columns.length; i++) {
                sheet.autoSizeColumn(i);
            }

            // Write the output to a file
            // FileOutputStream fileOut = new FileOutputStream("梦想在上杯第三十期实盘比赛_" + HolidayCopy.getRealToday() + ".xlsx");
//            workbook.write(fileOut);
//            fileOut.close();
//            // Closing the workbook
//            workbook.close();
            return workbook;
        }catch (Exception e){
            log.error(">>>>>>>>> 下载文件出错：",e);
        }
        return null;
    }
}
